/****** Object:  Stored Procedure [dbo].spPhieuNhapInsert Script Date: 6/12/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spPhieuNhapInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spPhieuNhapInsert]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		14/06/2012	SongTM					Innit created
*******************************************************************************/

CREATE PROCEDURE [dbo].[spPhieuNhapInsert]
(
	@SoPN nvarchar(14) output,
	@NgayNhapKho datetime,
	@NguoiNhap nvarchar(35),
	@NgayLapPhieu datetime,
	@NguoiLap nvarchar(35),
	@SoHD nvarchar(12),
	@LyDoNhap nvarchar(200),
	@GhiChu nvarchar(300),
	@MaNCC nvarchar(6),
	@MaKho nvarchar(5)
)
WITH ENCRYPTION

AS

SET NOCOUNT ON

	Declare @Stt char(3)
	Declare @y varchar(2)
	Declare @m varchar(2)
	
	set @y = RIGHT(YEAR(@NgayNhapKho),2)
	set @m =RIGHT(MONTH(@NgayNhapKho),2)
	
	if not exists (select * from tbl_PhieuNhap)
		Set @Stt=1
	else if not exists (select * from tbl_PhieuNhap where RIGHT(YEAR(NgayNhapKho),2) = @y and RIGHT(MONTH(NgayNhapKho),2) = @m )
		Set @Stt=1
	else
		Set @Stt=(select MAX(RIGHT(LEFT(SoPN,5),3)) from tbl_PhieuNhap where RIGHT(YEAR(NgayNhapKho),2) = @y and RIGHT(MONTH(NgayNhapKho),2) = @m )+1
	
	Set @Stt=REPLICATE('0',3-LEN(@Stt))+@Stt
	set @m = REPLICATE('0',2-LEN(@m))+@m
	Set @SoPN='PNxxx/mmyy/NP'
	Set @SoPN=REPLACE(@SoPN,'xxx',@Stt);
	Set @SoPN=REPLACE(@SoPN,'mm',@m);
	Set @SoPN=REPLACE(@SoPN,'yy',@y);

	INSERT INTO [tbl_PhieuNhap]
	(
		[SoPN],
		[NgayNhapKho],
		[NguoiNhap],
		[NgayLapPhieu],
		[NguoiLap],
		[SoHD],
		[LyDoNhap],
		[GhiChu],
		[MaNCC],
		[MaKho]
	)
	VALUES
	(
		@SoPN,
		@NgayNhapKho,
		@NguoiNhap,
		@NgayLapPhieu,
		@NguoiLap,
		@SoHD,
		@LyDoNhap,
		@GhiChu,
		@MaNCC,
		@MaKho
	)
GO


--GRANT EXECUTE ON [dbo].[spPhieuNhapInsert] TO [sa]
--GO
